﻿// Copyright 2006 - Ricardo Stuven (rstuven@gmail.com)
//
// This file is part of MsSqlSpatial.
// MsSqlSpatial is free software; you can redistribute it and/or modify
// it under the terms of the GNU Lesser General Public License as published by
// the Free Software Foundation; either version 2 of the License, or
// (at your option) any later version.
// 
// MsSqlSpatial is distributed in the hope that it will be useful,
// but WITHOUT ANY WARRANTY; without even the implied warranty of
// MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
// GNU Lesser General Public License for more details.

// You should have received a copy of the GNU Lesser General Public License
// along with MsSqlSpatial; if not, write to the Free Software
// Foundation, Inc., 59 Temple Place, Suite 330, Boston, MA  02111-1307  USA 

// THIS CODE HAS BEEN MODIFIED FROM SHARPMAP PROJECT
// Copyright 2005, 2006 - Morten Nielsen (www.iter.dk)
// SharpMap is free software; you can redistribute it and/or modify
// it under the terms of the GNU Lesser General Public License as published by
// the Free Software Foundation; either version 2 of the License, or
// (at your option) any later version.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.IO;
using GisSharpBlog.NetTopologySuite.Geometries;
using GisSharpBlog.NetTopologySuite.IO;
using Microsoft.SqlServer.Server;
using MsSqlSpatial;

public partial class StoredProcedures
{
    [SqlProcedure]
    public static void ImportFromShapefile(
        SqlString shapefile,
        [DefaultValue("")] SqlString schema_name,
        [DefaultValue("")] SqlString table_name,
        [DefaultValue("")] SqlString column_name,
        [DefaultValue(-1)] SqlInt32 srid,
        [DefaultValue("GEOMETRY")] SqlString type,
        [DefaultValue(false)] SqlBoolean appendRows
        )
    {
        long importedRows;
        TimeSpan elapsedTime;

        // TODO: Impersonation implementation pending. I'm getting the following error:
        // Msg 10312, Level 16, State 49, Procedure ImportFromShapefile, Line 0
        // .NET Framework execution was aborted. The UDP/UDF/UDT did not revert thread token.

//		WindowsImpersonationContext originalContext = null;
//		WindowsIdentity callerIdentity = SqlContext.WindowsIdentity;
//		
//		if (callerIdentity != null) 
//		{
//			originalContext = callerIdentity.Impersonate();
        ImportFromShapefileMain(shapefile.Value, schema_name.Value, table_name.Value, column_name.Value, srid.Value,
                                type.Value, "context connection=true", appendRows.IsTrue, out importedRows,
                                out elapsedTime);
        //		}
//		if (originalContext != null)
//			originalContext.Undo();

        SqlContext.Pipe.Send(string.Format("{0} records imported in {1}.", importedRows, FormatElapsedTime(elapsedTime)));
    }

    public static string FormatElapsedTime(TimeSpan elapsedTime)
    {
        if (elapsedTime.Minutes < 1)
            return string.Format("{0} seconds", elapsedTime.Seconds);
        else if (elapsedTime.Hours < 1)
            return string.Format("{0} minutes", elapsedTime.TotalMinutes.ToString("N"));
        else
            return elapsedTime.ToString();
    }

    public static void ImportFromShapefileMain(
        string shapefile,
        string schema_name,
        string table_name,
        string column_name,
        int srid,
        string type,
        string connectionString,
        bool appendRows,
        out long importedRows,
        out TimeSpan elapsedTime
        )
    {
        // Default table name to shapefile name.
        string filename = Path.GetFileName(shapefile);
        if (string.IsNullOrEmpty(table_name))
            table_name = filename.Remove(filename.Length - 4, 4); // remove .shp

        if (string.IsNullOrEmpty(column_name))
            column_name = "the_geom";

        using (IDataImportProcess process = new ShapefileImportProcess(shapefile))
            DataImportProcess(process, schema_name, table_name, column_name, srid, type, connectionString, appendRows,
                              out importedRows, out elapsedTime);
    }

    public static void DataImportProcess(
        IDataImportProcess process,
        string schemaName,
        string tableName,
        string columnName,
        int srid,
        string type,
        string connectionString,
        bool appendRows,
        out long importedRows,
        out TimeSpan elapsedTime
        )
    {
        importedRows = -1;
        elapsedTime = TimeSpan.Zero;

        DateTime start = DateTime.Now;

        DatabaseHelper.ContextConnectionString = connectionString;

        if (!SpatialRefSysTable.IsValidSRID(srid))
        {
            throw new InvalidSridException(srid);
        }

        using (SqlConnection connection = DatabaseHelper.ContextConnection)
        {
            connection.Open();
            using (SqlTransaction transaction = connection.BeginTransaction())
            {
                using (SqlCommand command = connection.CreateCommand())
                {
                    command.Transaction = transaction;
                    command.CommandTimeout = 0;
                    ExecuteCreateMetaTables(command);
                    schemaName = DatabaseHelper.CheckSchemaName(command, schemaName);
                    Dictionary<string, Type> columns = process.GetColumns();

                    if (appendRows)
                    {
                        GeometryTable.ExecuteDropIndexes(command, schemaName, tableName, columnName);
                        GeometryTable.ExecuteDropConstraintSRID(command, schemaName, tableName, columnName);
                        GeometryTable.ExecuteDropConstraintGeometryType(command, schemaName, tableName, columnName);
                        GeometryTable.ExecuteDropTrigger(command, schemaName, tableName, columnName);
                    }
                    else
                    {
                        //Try to drop table if it exists
                        try
                        {
                            DatabaseHelper.ExecuteDropTable(command, schemaName, tableName);
                        }
                        catch
                        {
                        }

                        //Create new table for storing the datasource
                        string createColumns = "";
                        foreach (var col in columns)
                        {
                            if (col.Value != typeof (String))
                                createColumns += ", " + col.Key + " " + Type2SqlType(col.Value);
                            else
                                createColumns += ", " + col.Key + " VARCHAR(4000)";
                        }
                        GeometryTable.ExecuteCreate(command, schemaName, tableName, columnName, createColumns);
                    }

                    CreateInsertCommand(command, schemaName, tableName, columnName, columns);
                    process.Execute(command, columnName, srid, out importedRows);
                    command.Parameters.Clear();

                    // Create indexes, meta-info and constraints
                    if (!appendRows)
                    {
                        GeometryColumnsTable.ExecuteAddMetaRecord(command, schemaName, tableName, columnName, srid, type);
                        ExecuteAddGeometryQueryFunctions(command, schemaName, tableName, columnName);
                    }
                    GeometryTable.ExecuteAddIndexes(command, schemaName, tableName, columnName);
                    GeometryTable.ExecuteAddConstraintSRID(command, schemaName, tableName, columnName, srid);
                    GeometryTable.ExecuteAddConstraintGeometryType(command, schemaName, tableName, columnName, type);
                    GeometryTable.ExecuteCreateTrigger(command, schemaName, tableName, columnName);

                    transaction.Commit();
                }
            }
        }

        elapsedTime = DateTime.Now - start;
    }

    private static void CreateInsertCommand(SqlCommand command, string schemaName, string tableName,
                                            string geometryColumnName, Dictionary<string, Type> columns)
    {
        //Create insert script
        string strSQL = " (";
        foreach (var col in columns)
            strSQL += "@" + col.Key + ",";

        strSQL += "@" + geometryColumnName + ")";
        strSQL = "INSERT INTO [" + schemaName + "].[" + tableName + "] " + strSQL.Replace("@", "") + " VALUES" + strSQL;

        DatabaseHelper.BuildCommand(command, strSQL);
        //Add feature columns parameters
        foreach (var col in columns)
            if (col.Value == typeof (string))
                command.Parameters.Add("@" + col.Key, SqlDbType.VarChar, 4000);
            else
                command.Parameters.Add("@" + col.Key, Type2SqlType(col.Value));

        //Add geometry column parameter
        command.Parameters.Add("@" + geometryColumnName, SqlDbType.VarBinary, int.MaxValue);

        command.Prepare();
    }

    /// <summary>
    /// Returns the name of the SqlServer datatype based on a .NET datatype
    /// </summary>
    /// <param name="t"></param>
    /// <returns></returns>
    private static SqlDbType Type2SqlType(Type t)
    {
        switch (t.ToString())
        {
            case "System.Boolean":
                return SqlDbType.Bit;
            case "System.Single":
                return SqlDbType.Real;
            case "System.Double":
                return SqlDbType.Float;
            case "System.Int16":
                return SqlDbType.SmallInt;
            case "System.Int32":
                return SqlDbType.Int;
            case "System.Int64":
                return SqlDbType.BigInt;
            case "System.DateTime":
                return SqlDbType.DateTime;
            case "System.Byte[]":
                return SqlDbType.Image;
            case "System.String":
                return SqlDbType.VarChar;
            default:
                throw (new NotSupportedException("Unsupported datatype '" + t.Name + "' found in datasource"));
        }
    }

    #region Nested type: IDataImportProcess

    public interface IDataImportProcess : IDisposable
    {
        Dictionary<string, Type> GetColumns();
        void Execute(SqlCommand command, string columnName, int srid, out long importedRows);
    }

    #endregion

    #region Nested type: ShapefileImportProcess

    public class ShapefileImportProcess : IDataImportProcess
    {
        private readonly ShapefileDataReader reader;
        private Dictionary<string, Type> columns;

        public ShapefileImportProcess(string filename)
        {
            reader = new ShapefileDataReader(filename, GeometryFactory.Default);
        }

        #region IDataImportProcess Members

        public Dictionary<string, Type> GetColumns()
        {
            if (columns == null)
            {
                columns = new Dictionary<string, Type>();
                foreach (DbaseFieldDescriptor col in reader.DbaseHeader.Fields)
                {
                    columns[col.Name] = col.Type;
                }
            }
            return columns;
        }

        public void Execute(SqlCommand command, string columnName, int srid, out long importedRows)
        {
            importedRows = 0;
            Dictionary<string, Type> columns = GetColumns();

            while (reader.Read())
            {
                int i = 0;
                foreach (string column in columns.Keys)
                {
                    command.Parameters["@" + column].Value = reader.GetValue(i);
                    i++;
                }
                command.Parameters["@" + columnName].Value = ConvertGeometry.ToSqlBytes(reader.Geometry, srid).Value;

                command.ExecuteNonQuery();
                importedRows++;
            }
        }

        public void Dispose()
        {
            reader.Dispose();
        }

        #endregion
    }

    #endregion
} ;